﻿using System;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace OperateDatabaseLibrary
{
    /// <summary>
    /// 操作数据类
    /// </summary>
    public class OperateDatabase
    {
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private readonly DbConnection _conn;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="conn">数据库连接对象</param>
        public OperateDatabase(DbConnection conn)
        {
            _conn = conn;

            if (_conn.State != ConnectionState.Open)
            {
                _conn.Open();
            }
        }

        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <param name="operateDatabaseCommand">操作数据库指令对象</param>
        /// <returns>查询结果封装为DataTable的对象</returns>
        public DataTable Select(OperateDatabaseCommand operateDatabaseCommand)
        {
            try
            {
                DataTable table = new DataTable();
                //校验
                if (string.IsNullOrEmpty(operateDatabaseCommand.CommandText))
                {
                    throw new ArgumentNullException(nameof(operateDatabaseCommand.CommandText), "CommandText属性不可以为Null或空字符串！");
                }

                var command = GetCommand(operateDatabaseCommand);
                var reader = GetReader(command);
                var columns = GetDataColumns(reader);

                //遍历获取数据
                while (reader.Read())
                {
                    DataRow row = table.NewRow();
                    foreach (var column in columns)
                    {
                        string columnName = column.ColumnName;
                        row[column] = reader[columnName];
                    }
                    table.Rows.Add(row);
                }
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 编辑数据表(包含“增、删、改”操作)
        /// </summary>
        /// <param name="operateDatabaseCommand">操作数据库指令对象</param>
        /// <returns>受影响的行数</returns>
        public int Edit(OperateDatabaseCommand operateDatabaseCommand)
        {
            try
            {
                //校验
                if (string.IsNullOrEmpty(operateDatabaseCommand.CommandText))
                {
                    throw new ArgumentNullException(nameof(operateDatabaseCommand.CommandText), "CommandText属性不可以为Null或空字符串！");
                }

                var command = GetCommand(operateDatabaseCommand);
                int affectedRowsCount = command.ExecuteNonQuery();
                return affectedRowsCount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取DbCommand对象
        /// </summary>
        /// <param name="operateDatabaseCommand">操作db指令对象</param>
        /// <returns>DbCommand对象</returns>
        private DbCommand GetCommand(OperateDatabaseCommand operateDatabaseCommand)
        { 
            //创建指令对象
            DbCommand command = _conn.CreateCommand();
            command.CommandText = operateDatabaseCommand.CommandText;
            //添加参数
            var dbParams = operateDatabaseCommand.DbParameters.ToArray();
            if (dbParams != null && dbParams.Any())
            {
                command.Parameters.AddRange(dbParams);
            }
            return command;
        }

        /// <summary>
        /// 获取reader对象
        /// </summary>
        /// <param name="command">DbCommand对象</param>
        /// <returns>reader对象</returns>
        private DbDataReader GetReader(DbCommand command)
        { 
            var reader = command.ExecuteReader();
            return reader;
        }

        /// <summary>
        /// 获取DataColumn数组对象
        /// </summary>
        /// <param name="reader">DbDataReader对象</param>
        /// <returns>DataColumn数组对象</returns>
        private DataColumn[] GetDataColumns(DbDataReader reader)
        {
            //获取字段总数
            int fieldsCount = reader.FieldCount;
            DataColumn[] dataColumns = new DataColumn[fieldsCount];
            for (int i = 0; i < fieldsCount; i++)
            {
                //获取字段名称
                string columnName = reader.GetName(i);
                //获取字段数据类型
                Type dataType = reader.GetFieldType(i);
                DataColumn dataColumn = new DataColumn(columnName, dataType);
                dataColumns[i] = dataColumn;
            }
            return dataColumns;
        }
    }
}
